package com.tp2.etna.distribucion2.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.tp2.etna.distribucion2.modelo.Transporte;

@Repository
public class TransporteDAOImpl implements TransporteDAO {

	@Autowired
    private DataSource dataSource;
	
	@Override
	public List<Transporte> obtenerTransporteDisponible(int hojaRutaid) {
		List<Transporte> transportes = new ArrayList<Transporte>();
		String query = " select t.codigo as codigo,"
				+ " t.placa as placa,"
				+ " t.pesoNeto as pesoNeto,"
				+ " t.cubicaje as cubicaje"
				+ " from transporte t"
				+ "	inner join disponibilidadTransporte dt"
				+ " on t.codigo = dt.idTransporte"
				+ " inner join Calendario c"
				+ " on c.codigo = dt.idCalendario"
				+ " where concat_ws('-',c.anio,c.mes,c.dia) = (select fecha from hojaRuta hr where hr.codigo = ?)"
				+ " and dt.disponible = '1'"
				+ " and t.pesoNeto >= (select sum(peso) from hojaRutadetalle hr where hr.idHojaRuta = ?)"
                + " and t.cubicaje >= (select sum(cubicaje) from hojaRutadetalle hr where hr.idHojaRuta = ?)"
                + " order by pesoNeto asc";     
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setInt(1, hojaRutaid);
            ps.setInt(2, hojaRutaid);
            ps.setInt(3, hojaRutaid);

            rs = ps.executeQuery();
            while(rs.next()){
            	Transporte transporte = new Transporte();
            	transporte.setCodigo(rs.getInt("codigo"));
            	transporte.setPlaca(rs.getString("placa"));
            	transporte.setCubicaje(rs.getDouble("cubicaje"));
            	transporte.setPesoNeto(rs.getDouble("pesoNeto"));
            	transportes.add(transporte);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return transportes;
	}

	@Override
	public boolean asignarTransporteHojaRuta(int hojaRutaid, int idTransporte) {
		String query = "update hojaRuta set idTransporte = ?,transporteDisponible=1"
				+ " where codigo = ?";
		String query2 = "update disponibilidadTransporte set disponible = 0"
				+ " where idTransporte = ?"
				+ " and idCalendario = "
				+ " (select codigo from Calendario c where concat_ws('-',c.anio,c.mes,c.dia) = "
						+ " (select fecha from hojaRuta hr where hr.codigo = ?))";
        Connection con = null;
        PreparedStatement ps = null;
        try{
            con = dataSource.getConnection();
            ps = con.prepareStatement(query);
            ps.setInt(1, idTransporte);
            ps.setInt(2,hojaRutaid);
            int out = ps.executeUpdate();
            if(out !=0){
            	ps.close();
            	ps = con.prepareStatement(query2);
            	ps.setInt(1, idTransporte);
            	ps.setInt(2,hojaRutaid);
            	int result = ps.executeUpdate();
            	if(result == 1)
            	return true;
            	
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
	}

}
